﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using syc.Helper;
using System.Drawing;

namespace syc.Sale.Report
{
    public partial class 油管销售日报表 : System.Web.UI.Page
    {

        private string sql = @"SELECT 
dbo.产品订货单位.名称 AS 接货单位,
dbo.产品.品名, 
COUNT(*) AS 当日车数,
SUM(c.总支数) AS 当日支数, 
SUM(c.总重量) AS 当日重量, 
	(SELECT COUNT(*) AS Expr1
	FROM dbo.产品出库单
	WHERE  (MONTH(日期)= *月份*) AND 
		   (YEAR(日期) = *年份*) AND
           日期 <= '*日期*' AND
		   产品类别ID=c.产品类别ID AND
           合同ID=c.合同ID           
		   ) 
		   AS 月车数,
	(SELECT SUM(总支数) AS Expr1
	FROM dbo.产品出库单
	WHERE  (MONTH(日期)= *月份*) AND 
		   (YEAR(日期) = *年份*) AND
           日期 <= '*日期*' AND
		   产品类别ID=c.产品类别ID AND
            合同ID=c.合同ID          
		   ) 
		   AS 月支数,
	(SELECT SUM(总重量) AS Expr1
	FROM dbo.产品出库单
	WHERE  (MONTH(日期)= *月份*) AND 
		   (YEAR(日期) = *年份*) AND
           日期 <= '*日期*' AND
		   产品类别ID=c.产品类别ID AND
            合同ID=c.合同ID          
		   ) 
		   AS 月重量,
	(SELECT COUNT(*) AS Expr1
	FROM dbo.产品出库单
	WHERE  (YEAR(日期) = *年份*) AND
           日期 <= '*日期*' AND
		   产品类别ID=c.产品类别ID AND
           合同ID=c.合同ID          
		   ) 
		   AS 年车数,
	(SELECT SUM(总支数) AS Expr1
	FROM dbo.产品出库单
	WHERE  (YEAR(日期) = *年份*) AND
           日期 <= '*日期*' AND
		   产品类别ID=c.产品类别ID AND
           合同ID=c.合同ID          
		   ) 
		   AS 年支数,
	(SELECT SUM(总重量) AS Expr1
	FROM dbo.产品出库单
	WHERE  (YEAR(日期) = *年份*) AND
           日期 <= '*日期*' AND
		   产品类别ID=c.产品类别ID AND
           合同ID=c.合同ID          
		   ) 
		   AS 年重量
FROM         
dbo.产品 INNER JOIN
dbo.产品出库单 AS c ON dbo.产品.ID = c.产品类别ID INNER JOIN
dbo.产品销售合同 ON c.合同ID = dbo.产品销售合同.ID INNER JOIN
dbo.产品订货单位 ON dbo.产品销售合同.订货单位ID = dbo.产品订货单位.ID
WHERE
c.日期='*日期*'
GROUP BY
dbo.产品订货单位.名称,
dbo.产品.品名,
c.产品类别ID,
c.合同ID";
        protected static DateTime date;


        protected void Page_Load(object sender, EventArgs e)
        {

            this.Message.Text = "·准备就绪";
            if (!this.IsPostBack)
            {
                bool ifsuccedParsed = DateTime.TryParse(RiQi.Text, out date);

                //如果解析成功则使用，不成功则使用当天的时间
                if (ifsuccedParsed)
                {

                }
                else
                {
                    date = DateTime.Today;
                }
                RiQi.Text = date.ToString("yyyy年MM月dd日");
                sql = sql.Replace("*日期*", date.ToString("yyyy-MM-dd"));
                sql = sql.Replace("*年份*", date.Year.ToString());
                sql = sql.Replace("*月份*", date.Month.ToString());
                this.SqlDataSource1.SelectCommand = sql;
                this.ViewState["SelectCommand"] = sql;
            }
            this.SqlDataSource1.SelectCommand = (string)this.ViewState["SelectCommand"];
        }

        protected void RiQi_TextChanged(object sender, EventArgs e)
        {
            bool ifsuccedParsed = DateTime.TryParse(RiQi.Text, out date);

            //如果解析成功则使用，不成功则使用当天的时间
            if (ifsuccedParsed)
            {

            }
            else
            {
                date = DateTime.Today;
            }
            sql = sql.Replace("*日期*", date.ToString("yyyy-MM-dd"));
            sql = sql.Replace("*年份*", date.Year.ToString());
            sql = sql.Replace("*月份*", date.Month.ToString());
            this.SqlDataSource1.SelectCommand = sql;
            this.ViewState["SelectCommand"] = sql;

            this.SqlDataSource1.SelectCommand = (string)this.ViewState["SelectCommand"];
        }


        //设置表头
        protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
        {

            switch (e.Row.RowType)
            {
                case DataControlRowType.Header:
                    //第一行表头
                    TableCellCollection tcHeader = e.Row.Cells;
                    tcHeader.Clear();
                    tcHeader.Add(new TableHeaderCell());
                    tcHeader[0].Attributes.Add("rowspan", "2"); //跨2Row
                    tcHeader[0].Text = "接货单位";
                    tcHeader.Add(new TableHeaderCell());
                    tcHeader[1].Attributes.Add("rowspan", "2"); //跨2Row
                    tcHeader[1].Text = "品名";
                    tcHeader.Add(new TableHeaderCell());
                    tcHeader[2].Attributes.Add("colspan", "3"); //跨3Col
                    tcHeader[2].Text = "当日数据";
                    tcHeader.Add(new TableHeaderCell());
                    tcHeader[3].Attributes.Add("colspan", "3"); //跨3Col
                    tcHeader[3].Text = "当月数据";
                    tcHeader.Add(new TableHeaderCell());
                    tcHeader[4].Attributes.Add("colspan", "3"); //跨3Col
                    tcHeader[4].Text = "当年数据</th></tr><tr>";

                    //第二行表头
                    tcHeader.Add(new TableHeaderCell());
                    tcHeader[5].Attributes.Add("colspan", "1");
                    tcHeader[5].Text = "车数";
                    tcHeader.Add(new TableHeaderCell());
                    tcHeader[6].Attributes.Add("colspan", "1");
                    tcHeader[6].Text = "支数";
                    tcHeader.Add(new TableHeaderCell());
                    tcHeader[7].Attributes.Add("colspan", "1");
                    tcHeader[7].Text = "重量(吨)";
                    tcHeader.Add(new TableHeaderCell());
                    tcHeader[8].Attributes.Add("colspan", "1");
                    tcHeader[8].Text = "车数";
                    tcHeader.Add(new TableHeaderCell());
                    tcHeader[9].Attributes.Add("colspan", "1");
                    tcHeader[9].Text = "支数";
                    tcHeader.Add(new TableHeaderCell());
                    tcHeader[10].Attributes.Add("colspan", "1");
                    tcHeader[10].Text = "重量(吨)";
                    tcHeader.Add(new TableHeaderCell());
                    tcHeader[11].Attributes.Add("colspan", "1");
                    tcHeader[11].Text = "车数";
                    tcHeader.Add(new TableHeaderCell());
                    tcHeader[12].Attributes.Add("colspan", "1");
                    tcHeader[12].Text = "支数";
                    tcHeader.Add(new TableHeaderCell());
                    tcHeader[13].Attributes.Add("colspan", "1");
                    tcHeader[13].Text = "重量(吨)</th></tr><tr>";

                    break;

            }

        }

        protected void Button_New_Click(object sender, EventArgs e)
        {
            bool ifsuccedParsed = DateTime.TryParse(RiQi.Text, out date);

            //如果解析成功则使用，不成功则使用当天的时间
            if (ifsuccedParsed)
            {

            }
            else
            {
                date = DateTime.Today;
            }
            sql = sql.Replace("*日期*", date.ToString("yyyy-MM-dd"));
            sql = sql.Replace("*年份*", date.Year.ToString());
            sql = sql.Replace("*月份*", date.Month.ToString());
            this.SqlDataSource1.SelectCommand = sql;
            this.ViewState["SelectCommand"] = sql;

            this.SqlDataSource1.SelectCommand = (string)this.ViewState["SelectCommand"];
        }


    }





}
